/**
 * 
 */
package com.common.utils;

import java.io.File;
import java.util.LinkedList;
import java.util.Locale;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * 
 * @author Bin.jiang
 *
 */
public class ExcelUtil {
	/**
	 * export defined excel file. 
	 * @param path	File Path
	 * @param filename File Name
	 * @param header	Column name of content
	 * @param widths	Column's Width
	 * @param content	File Content, content consist of 'LinkedList'
	 * @return File
	 */
	public static File export(String path,String filename,LinkedList header,LinkedList widths,LinkedList content) throws Exception {
		File file = null;
		
		/*start validate*/
		if ((path==null) || (path.equals(""))||(filename==null)||(filename.equals(""))) throw new Exception("Path or Filename is invalid.");
		File directory = new File(path);
		if ((!directory.isDirectory()) || (!directory.exists())) throw new Exception("Path is invalid.");	
		
		/*end validate*/
		
		/*start file*/
		String fullname = path+File.separator+filename;
		file = new File(fullname);	

		if (file.exists()) file.delete();
		
		WorkbookSettings ws = new WorkbookSettings();
		ws.setLocale(new Locale("en", "EN"));
		WritableWorkbook workbook = Workbook.createWorkbook(file, ws);
		 
		
		WritableSheet writableSheet = null;
		/*end file*/
		writableSheet = workbook.createSheet("Export",0);		
		/*start header*/
		WritableFont writableFont4Header = new WritableFont(WritableFont.ARIAL, 10);
		WritableCellFormat writableCellFormat4Header = new WritableCellFormat(writableFont4Header);
		writableCellFormat4Header.setBorder(Border.ALL, BorderLineStyle.THIN);
		writableCellFormat4Header.setBackground(Colour.GREY_25_PERCENT);
		writableCellFormat4Header.setAlignment(Alignment.CENTRE);
		writableCellFormat4Header.setVerticalAlignment(VerticalAlignment.CENTRE);
		writableCellFormat4Header.setWrap(true);
		
		if ((header!=null) && (!header.isEmpty()) && (widths!=null) && (!widths.isEmpty()) && (header.size()==widths.size())) {
			for (int i=0; i < header.size(); i++) {
				String h = (String)header.get(i);
				Integer w = (Integer)widths.get(i);
				
				Label label4Header = new Label(i, 0, h, writableCellFormat4Header);
				writableSheet.setColumnView(i, w.intValue());
				writableSheet.setRowView(i, 20 * 20);
				writableSheet.addCell(label4Header);
			}
		}else {
			throw new Exception("header or widths is invalid.");
		}
		/*end header*/
		
		/*start content*/
		WritableFont writableFont4Content = new WritableFont(WritableFont.ARIAL, 9);
		WritableCellFormat writableCellFormat4Content = new WritableCellFormat(writableFont4Content);
		writableCellFormat4Content.setBorder(Border.ALL, BorderLineStyle.THIN);
		writableCellFormat4Content.setAlignment(Alignment.CENTRE);
		writableCellFormat4Content.setVerticalAlignment(VerticalAlignment.CENTRE);
		writableCellFormat4Content.setWrap(true);
		
		if ((content!=null) && (!content.isEmpty())) {
			for (int i=0; i < content.size(); i++) {
				LinkedList row = (LinkedList)content.get(i);
				if ((row!=null) && (!row.isEmpty())) {
					for (int j=0; j < row.size(); j++) {
						String value = (String)row.get(j);
						Label label4Content = new Label(j, i+1, value, writableCellFormat4Content);
						writableSheet.setRowView(i+1, 20 * 20);
						writableSheet.addCell(label4Content);
					}
				}
			}
		}else {
			throw new Exception("header or widths is invalid.");
		}
		/*end content*/
		
		workbook.write();
		workbook.close();
		
		return file;
	}
	/**
	 * export defined excel file. 
	 * @param path	File Path
	 * @param filename File Name
	 * @param title	File Title
	 * @param header	Column name of content
	 * @param widths	Column's Width
	 * @param content	File Content, content consist of 'LinkedList'
	 * @return File
	 */
	public static File export(String path,String filename,String title,LinkedList header,LinkedList widths,LinkedList content) throws Exception {
		File file = null;
		int cols = 1;
		
		/*start validate*/
		if ((path==null) || (path.equals(""))||(filename==null)||(filename.equals(""))) throw new Exception("Path or Filename is invalid.");
		File directory = new File(path);
		if ((!directory.isDirectory()) || (!directory.exists())) throw new Exception("Path is invalid.");	
		
		if ((header!=null) && (!header.isEmpty())) cols = header.size();
		/*end validate*/ 
		/*start file*/ 
		String fullname = path+File.separator+filename; 
		file = new File(fullname);	
		if (file.exists()) file.delete();
		
		WorkbookSettings ws = new WorkbookSettings();
		ws.setLocale(new Locale("en", "EN"));
		WritableWorkbook workbook = Workbook.createWorkbook(file, ws);
		
		WritableSheet writableSheet = null;
		/*end file*/
		
		/*start title*/
		WritableFont writableFont4Title = new WritableFont(WritableFont.ARIAL, 18);
		writableFont4Title.setBoldStyle(WritableFont.BOLD);
		WritableCellFormat writableCellFormat4Title = new WritableCellFormat(writableFont4Title);
		writableCellFormat4Title.setBorder(Border.ALL, BorderLineStyle.THIN);
		writableCellFormat4Title.setAlignment(Alignment.CENTRE);
		writableCellFormat4Title.setVerticalAlignment(VerticalAlignment.CENTRE);
		writableCellFormat4Title.setWrap(true);
		
		writableSheet = workbook.createSheet(title,0);
		
		Label label4Title = new Label(0, 0, title, writableCellFormat4Title);
		writableSheet.setColumnView(0, 15);
		writableSheet.setRowView(0, 25 * 25);
		writableSheet.mergeCells(0, 0, cols-1, 0);
		writableSheet.addCell(label4Title);
		/*end title*/
		
		/*start header*/
		WritableFont writableFont4Header = new WritableFont(WritableFont.ARIAL, 10);
		WritableCellFormat writableCellFormat4Header = new WritableCellFormat(writableFont4Header);
		writableCellFormat4Header.setBorder(Border.ALL, BorderLineStyle.THIN);
		writableCellFormat4Header.setBackground(Colour.GREY_25_PERCENT);
		writableCellFormat4Header.setAlignment(Alignment.CENTRE);
		writableCellFormat4Header.setVerticalAlignment(VerticalAlignment.CENTRE);
		writableCellFormat4Header.setWrap(true);
		
		if ((header!=null) && (!header.isEmpty()) && (widths!=null) && (!widths.isEmpty()) && (header.size()==widths.size())) {
			for (int i=0; i < header.size(); i++) {
				String h = (String)header.get(i);
				Integer w = (Integer)widths.get(i);
				
				Label label4Header = new Label(i, 1, h, writableCellFormat4Header);
				writableSheet.setColumnView(i, w.intValue());
				writableSheet.setRowView(i, 20 * 20);
				writableSheet.addCell(label4Header);
			}
		}else {
			throw new Exception("header or widths is invalid.");
		}
		/*end header*/
		
		/*start content*/
		WritableFont writableFont4Content = new WritableFont(WritableFont.ARIAL, 9);
		WritableCellFormat writableCellFormat4Content = new WritableCellFormat(writableFont4Content);
		writableCellFormat4Content.setBorder(Border.ALL, BorderLineStyle.THIN);
		writableCellFormat4Content.setAlignment(Alignment.CENTRE);
		writableCellFormat4Content.setVerticalAlignment(VerticalAlignment.CENTRE);
		writableCellFormat4Content.setWrap(true);
				
		if ((content!=null) && (!content.isEmpty())) {
			for (int i=0; i < content.size(); i++) {
				LinkedList row = (LinkedList)content.get(i);
				if ((row!=null) && (!row.isEmpty())) {
					for (int j=0; j < row.size(); j++) {
						String value = (String)row.get(j);
						Label label4Content = new Label(j, i+2, value, writableCellFormat4Content);
						writableSheet.setRowView(i+1, 20 * 20);
						writableSheet.addCell(label4Content);
					}
				}
			}
		}
		/*end content*/
		
		workbook.write();
		workbook.close();
		
		return file;
	}
	/**
	 * export defined excel file. 
	 * @param path	File Path
	 * @param filename File Name
	 * @param title	File Title
	 * @param height4Title Height of Title
	 * @param header	Column name of content
	 * @param widths	Column's Width
	 * @param height4Header Row's Height 
	 * @param content	File Content, content consist of 'LinkedList'
	 * @param height4Rows Rows Height
	 * @return File
	 */
	public static File export(String path,String filename,String title,int height4Title,LinkedList header,LinkedList widths,int height4Header,LinkedList content,int height4Rows) throws Exception {
		File file = null;
		int cols = 1;
		
		/*start validate*/
		if ((path==null) || (path.equals(""))||(filename==null)||(filename.equals(""))) throw new Exception("Path or Filename is invalid.");
		File directory = new File(path);
		if ((!directory.isDirectory()) || (!directory.exists())) throw new Exception("Path is invalid.");	
		
		if ((header!=null) && (!header.isEmpty())) cols = header.size();
		/*end validate*/
		
		/*start file*/
		String fullname = path+File.separator+filename;
		file = new File(fullname);	

		if (file.exists()) file.delete();
		
		WorkbookSettings ws = new WorkbookSettings();
		ws.setLocale(new Locale("en", "EN"));
		WritableWorkbook workbook = Workbook.createWorkbook(file, ws);
		
		WritableSheet writableSheet = null;
		/*end file*/
		
		/*start title*/
		WritableFont writableFont4Title = new WritableFont(WritableFont.ARIAL, 18);
		writableFont4Title.setBoldStyle(WritableFont.BOLD);
		WritableCellFormat writableCellFormat4Title = new WritableCellFormat(writableFont4Title);
		writableCellFormat4Title.setBorder(Border.ALL, BorderLineStyle.THIN);
		writableCellFormat4Title.setAlignment(Alignment.CENTRE);
		writableCellFormat4Title.setVerticalAlignment(VerticalAlignment.CENTRE);
		writableCellFormat4Title.setWrap(true);
		
		writableSheet = workbook.createSheet(title,0);
		
		Label label4Title = new Label(0, 0, title, writableCellFormat4Title);
		writableSheet.setColumnView(0, 15);
		writableSheet.setRowView(0, 25 * 25);
		writableSheet.mergeCells(0, 0, cols-1, 0);
		writableSheet.addCell(label4Title);
		/*end title*/
		
		/*start header*/
		WritableFont writableFont4Header = new WritableFont(WritableFont.ARIAL, 10);
		WritableCellFormat writableCellFormat4Header = new WritableCellFormat(writableFont4Header);
		writableCellFormat4Header.setBorder(Border.ALL, BorderLineStyle.THIN);
		writableCellFormat4Header.setBackground(Colour.GREY_25_PERCENT);
		writableCellFormat4Header.setAlignment(Alignment.CENTRE);
		writableCellFormat4Header.setVerticalAlignment(VerticalAlignment.CENTRE);
		writableCellFormat4Header.setWrap(true);
		
		if ((header!=null) && (!header.isEmpty()) && (widths!=null) && (!widths.isEmpty()) && (header.size()==widths.size())) {
			for (int i=0; i < header.size(); i++) {
				String h = (String)header.get(i);
				Integer w = (Integer)widths.get(i);
				
				Label label4Header = new Label(i, 1, h, writableCellFormat4Header);
				writableSheet.setColumnView(i, w.intValue());
				writableSheet.setRowView(i, height4Header * height4Header);
				writableSheet.addCell(label4Header);
			}
		}else {
			throw new Exception("header or widths is invalid.");
		}
		/*end header*/
		
		/*start content*/
		WritableFont writableFont4Content = new WritableFont(WritableFont.ARIAL, 9);
		WritableCellFormat writableCellFormat4Content = new WritableCellFormat(writableFont4Content);
		writableCellFormat4Content.setBorder(Border.ALL, BorderLineStyle.THIN);
		writableCellFormat4Content.setAlignment(Alignment.CENTRE);
		writableCellFormat4Content.setVerticalAlignment(VerticalAlignment.CENTRE);
		writableCellFormat4Content.setWrap(true);
		
		if ((content!=null) && (!content.isEmpty())) {
			for (int i=0; i < content.size(); i++) {
				LinkedList row = (LinkedList)content.get(i);
				if ((row!=null) && (!row.isEmpty())) {
					for (int j=0; j < row.size(); j++) {
						String value = (String)row.get(j);
						Label label4Content = new Label(j, i+2, value, writableCellFormat4Content);
						writableSheet.setRowView(i+1, height4Rows * height4Rows);
						writableSheet.addCell(label4Content);
					}
				}
			}
		}else {
			throw new Exception("header or widths is invalid.");
		}
		/*end content*/
		
		workbook.write();
		workbook.close();
		
		return file;
	}
	/**
	 * @param args
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static void main(String[] args) throws Exception{
		// TODO Auto-generated method stub
		LinkedList headers = new LinkedList();
		headers.add("用户名");headers.add("姓名");headers.add("地址");headers.add("性别");
		LinkedList widths = new LinkedList();
		widths.add(new Integer(10));widths.add(new Integer(15));widths.add(new Integer(30));widths.add(new Integer(5));
		
		LinkedList content = new LinkedList();
		LinkedList row = new LinkedList();
		LinkedList row2 = new LinkedList();
		row.add("chx001");row.add("江彬");row.add("南京西路1680号4608");row.add("男");
		content.add(row);
//		row.clear();
		row2.add("chx002");row2.add("袁彬");row2.add("中山西路930号6A");row2.add("男");
		content.add(row2);
		//File file = Excel.export("c:\\","test.xls","文件测试用例",30,headers,widths,25,content,25);
		//File file = Excel.export("c:\\","test.xls","文件测试用例",headers,widths,content);
		File file = ExcelUtil.export("c:\\","test.xls",headers,widths,content);
	}
}
